package cn.com.zzl.util;


import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Description：jxl导入导出Excel2003工具类（xls）
 * Created by 朱正磊 on 2017-04-22 10:30.
 */
public class ExcelUtil {

	/**
	 * @Desc 导出Excel（导出到本地文件系统）
	 * @param list 数据源
	 * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系
	 * 如果获取的是引用对象的属性，则英文属性使用类似于EL表达式的格式，
	 * 如：list中存放的都是student，student中又有college属性（引用对象），而我们需要学院名称，则可以这样写
	 * fieldMap.put("college.collegeName","学院名称")
	 * @param sheetName 工作表的名称
	 * @param sheetSize 每个工作表中记录的最大个数
	 * @param out 导出流
	 * @param <T> 泛型
	 * @throws ExcelException 自定义Excel异常
	 */
	public static <T> void listToExcel(List<T> list,
									   LinkedHashMap<String, String> fieldMap,
									   String sheetName,
									   int sheetSize,
									   OutputStream out) throws ExcelException{
		if(list == null || list.size() == 0){
			throw new ExcelException("数据源中没有任何数据");
		}

		if(sheetSize > 65535 || sheetSize < 1){
			sheetSize = 65535;
		}

		try {//进行导出Excel操作
			WritableWorkbook wwb = Workbook.createWorkbook(out);//创建工作簿并发送到OutputStream指定的地方
			//因为2003的Excel一个工作表最多可以有65536条记录，除去列头剩下65535条
			//所以如果记录太多，需要放到多个工作表中，其实就是个分页的过程
			//1.计算一共有多少个工作表
			double sheetNum = Math.ceil(list.size() / new Integer(sheetSize).doubleValue());
			//2.创建相应的工作表，并向其中填充数据
			for(int i=0; i<sheetNum; i++){
				if(1 == sheetNum){//如果只有一个工作表的情况
					//创建工作表
					WritableSheet sheet = wwb.createSheet(sheetName, i);
					//填充工作表
					fillSheet(sheet, list, fieldMap, 0, list.size()-1);
				}else{//有多个工作表的情况
					//创建工作表
					WritableSheet sheet = wwb.createSheet(sheetName+(i+1), i);
					//获取开始索引和结束索引
					int firstIndex = i * sheetSize;
					int lastIndex = (i+1) * sheetSize-1 > list.size()-1 ? list.size()-1 : (i+1) * sheetSize-1;
					//填充工作表
					fillSheet(sheet, list, fieldMap, firstIndex, lastIndex);
				}
			}
			wwb.write();
			wwb.close();
		}catch (Exception e) {
			e.printStackTrace();
			if(e instanceof ExcelException){//如果是ExcelException，则直接抛出
				throw (ExcelException)e;
			}else{//否则将其它异常包装成ExcelException再抛出
				throw new ExcelException("导出Excel失败！");
			}
		}
	}

	/**
	 * @Desc 导出Excel（导出到本地文件系统）
	 * @param list 数据源
	 * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系
	 * @param sheetName 工作表的名称
	 * @param out 导出流
	 * @param <T> 泛型
	 * @throws ExcelException
	 */
	public static <T> void listToExcel(List<T> list,
									   LinkedHashMap<String, String> fieldMap,
									   String sheetName,
									   OutputStream out) throws ExcelException{
		listToExcel(list,fieldMap,sheetName,65535,out);
	}

	/**
	 * @Desc 导出Excel（导出到浏览器即下载）
	 * @param list 数据源
	 * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系
	 * @param sheetName 工作表的名称
	 * @param sheetSize 每个工作表中记录的最大个数
	 * @param response 使用response可以导出到浏览器
	 * @param <T>
	 * @throws ExcelException
	 */
	public static <T> void listToExcel(List<T> list,
									   LinkedHashMap<String, String> fieldMap,
									   String sheetName,
									   int sheetSize,
									   HttpServletResponse response) throws ExcelException{
		//设置默认文件名为当前时间：年月日时分秒
		String fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());

		//设置response头信息
		response.reset();
		response.setContentType("application/vnd.ms-excel");//改成输出excel文件
		response.setHeader("Content-disposition","attachment; filename=" + fileName + ".xls" );

		//创建工作簿并发送到浏览器
		try {
			OutputStream out = response.getOutputStream();
			listToExcel(list, fieldMap, sheetName, sheetSize, out);
		} catch (Exception e) {
			e.printStackTrace();
			if (e instanceof ExcelException) {//如果是ExcelException，则直接抛出
				throw (ExcelException) e;
			} else {//否则将其它异常包装成ExcelException再抛出
				throw new ExcelException("导出Excel失败");
			}
		}
	}

	/**
	 * @Desc 导出Excel（导出到浏览器即下载）
	 * @param list
	 * @param fieldMap
	 * @param sheetName
	 * @param response
	 * @param <T>
	 * @throws ExcelException
	 */
	public static <T> void listToExcel(List<T> list,
									   LinkedHashMap<String, String> fieldMap,
									   String sheetName,
									   HttpServletResponse response) throws ExcelException{
		listToExcel(list, fieldMap, sheetName, 65535, response);
	}

	/**
	 * @Desc 导入Excel（将Excel转化为List）
	 * @param in 承载着Excel的输入流
	 * @param sheetName 要导入的工作表名称
	 * @param entityClass List中对象的类型（Excel中的每一行都要转化为该类型的对象）
	 * @param fieldMap Excel中的中文列头和类的英文属性的对应关系Map，key是英文，value是中文
	 * @param uniqueFields 指定业务主键组合（即复合主键），这些列的组合不能重复
	 * @param <T> 泛型
	 * @return list集合
	 * @throws ExcelException
	 */
	public static <T> List<T> excelToList(InputStream in,
									      String sheetName,
									      Class<T> entityClass,
									      LinkedHashMap<String, String> fieldMap,
									      String[] uniqueFields) throws ExcelException{
		//定义要返回的list
		List<T> resultList = new ArrayList<T>();

		try {
			//根据Excel数据源创建WorkBook
			Workbook wb = Workbook.getWorkbook(in);
			//获取工作表
			Sheet sheet = wb.getSheet(sheetName);
			//获取工作表的有效行数
			int realRows = 0;
			for(int row=0; row<sheet.getRows(); row++){
				int nullCols = 0;
				for(int column=0; column<sheet.getColumns(); column++){
					Cell currentCell = sheet.getCell(column,row);
					if(currentCell == null || "".equals(currentCell.getContents())){
						nullCols++;
					}
				}
				if(nullCols == sheet.getColumns()){
						break;
				}else{
					realRows++;
				}
			}
			if(realRows <= 1){//如果Excel中没有数据则提示错误
				throw new ExcelException("Excel文件中没有任何数据");
			}

			Cell[] firstRow = sheet.getRow(0);
			String[] excelFieldNames = new String[firstRow.length];
			//获取Excel中的列名
			for(int i=0; i<firstRow.length; i++){
				excelFieldNames[i] = firstRow[i].getContents().trim();
			}

			//判断需要的字段在Excel中是否都存在
			boolean isExist = true;
			List<String> excelFieldList = Arrays.asList(excelFieldNames);
			//for(String enName : fieldMap.keySet()){//key英文，value中文
			for(String cnName : fieldMap.values()){//key英文，value中文
				//String cnName = fieldMap.get(enName);
				if(!excelFieldList.contains(cnName)){//如果Excel中缺少需要的字段
					isExist = false;
					break;
				}
			}
			//如果有列名不存在，则抛出异常，提示错误
			if(!isExist){
				throw new ExcelException("Excel中缺少必要的字段，或字段名称有误");
			}

			//将列名和列号放入Map中,这样通过列名就可以拿到列号
			LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
			for(int i=0; i<excelFieldNames.length; i++){
				colMap.put(excelFieldNames[i], firstRow[i].getColumn());
			}

			//判断是否有重复行
			//1.获取uniqueFields指定的列
			Cell[][] uniqueCells = new Cell[uniqueFields.length][];//复合主键列
			for(int i=0; i<uniqueFields.length; i++){
				int col = colMap.get(uniqueFields[i]);//根据复合主键列名（中文）得到列号
				uniqueCells[i] = sheet.getColumn(col);//根据列号获得复合主键列（二维数组，给每一行赋值一维数组）
			}

			//2.从指定列中寻找重复行
			for(int row=1; row<realRows; row++){
				int nullCols = 0;
				for(int column=0; column<uniqueFields.length; column++){
					String currentContent = uniqueCells[column][row].getContents();
					//findCell(String content, int firstCol, int firstRow, int lastCol, int lastRow, boolean reverse)
					//content是匹配内容，firstCol是搜索开始列，firstRow是搜索开始行，同样lastCol，lastRow是结束列和结束行
					//reverse是指示是否执行反向搜索，反向搜索是从行、列数大的开始向小的方向搜索。true是执行反向搜索。
					//返回第一个匹配单元格。
					Cell sameCell = sheet.findCell(currentContent,
							uniqueCells[column][row].getColumn(),
							uniqueCells[column][row].getRow() + 1,
							uniqueCells[column][row].getColumn(),
							uniqueCells[column][realRows - 1].getRow(),true);
					if(sameCell != null){
						nullCols++;
					}
				}
				if(nullCols == uniqueFields.length){
					throw new ExcelException("Excel中有重复行，请检查");
				}
			}

			//将sheet转换为list
			for(int i=1; i<realRows; i++){
				//新建要转换的对象
				T entity = entityClass.newInstance();
				//给对象中的字段赋值
				for(Map.Entry<String, String> entry : fieldMap.entrySet()){
					//获取英文字段名
					String enNormalName = entry.getKey();
					//获取中文字段名
					String cnNormalName = entry.getValue();
					//根据中文字段名获取列号
					int col = colMap.get(cnNormalName);
					//获取当前单元格中的内容
					String content = sheet.getCell(col, i).getContents().trim();
					//给对象赋值
					setFieldValueByName(enNormalName, content, entity);
				}
				resultList.add(entity);
			}
		} catch(Exception e){
			e.printStackTrace();
			if(e instanceof ExcelException){//如果是ExcelException，则直接抛出
				throw (ExcelException)e;
			}else{//否则将其它异常包装成ExcelException再抛出
				e.printStackTrace();
				throw new ExcelException("导入Excel失败");
			}
		}
		return resultList;
	}

	/**************************************【private辅助的私有方法】**************************************/

	/**
	 * （导出，即list到Excel）
	 * @Desc 向工作表中填充数据
	 * @param sheet 工作表
	 * @param list 数据源
	 * @param fieldMap 中英文字段对应关系的Map，key英文，value中文
	 * @param firstIndex 行开始索引
	 * @param lastIndex 行结束索引，（lastIndex - firstIndex + 1）决定了工作表总共有多少行数据
	 * @param <T> 泛型
	 * @throws Exception
	 */
	private static <T> void fillSheet(WritableSheet sheet,
									  List<T> list,
									  LinkedHashMap<String,String> fieldMap,
									  int firstIndex,
									  int lastIndex) throws Exception{
		//定义存放英文字段名和中文字段名的数组（表头）
		String[] enFields = new String[fieldMap.size()];
		String[] cnFields = new String[fieldMap.size()];
		//填充数组（表头）
		int count=0;
		for(Map.Entry<String,String> entry : fieldMap.entrySet()){
			enFields[count] = entry.getKey();
			cnFields[count] = entry.getValue();
			count++;
		}
		//填充表头
		for(int i=0; i<cnFields.length; i++){
			Label label = new Label(i,0,cnFields[i]);
			sheet.addCell(label);
		}

		//填充内容（表体）
		int rowNo=1;
		for(int index = firstIndex; index<=lastIndex; index++){
			//获取单个对象
			T item = list.get(index);
			for(int i=0; i<enFields.length; i++){
				Object objValue = getFieldValueByNameSequence(enFields[i], item);
				String fieldValue;
				if(objValue instanceof Date){//日期时间
					fieldValue = CalendarUtil.dateToString((Date)objValue,"yyyy-MM-dd HH:mm:ss");
				}else{
					fieldValue = objValue == null ? "" : objValue.toString();
				}
				Label label = new Label(i,rowNo,fieldValue);
				sheet.addCell(label);
			}
			rowNo++;
		}
		//设置自动列宽
		setColumnAutoSize(sheet, 5);
	}

	/**
	 * （导出，即list到Excel）
	 * @Desc 根据带路径或不带路径的属性名获取属性值，
	 * 			即接受简单属性名，如userName等，又接受带路径的属性名，如student.department.name等
	 * @param fieldNameSequence 带路径的属性名或简单属性名
	 * @param o 对象
	 * @return 属性值
	 * @throws Exception
	 */
	private static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception{
		Object value ;
		String[] attributes = fieldNameSequence.split("\\.");//将fieldNameSequence根据"."进行拆分
		if(attributes.length == 1){
			value = getFieldValueByName(fieldNameSequence, o);
		}else{
			//根据属性名获取属性对象
			Object fieldObj = getFieldValueByName(attributes[0], o);
			//说明：其中indexOf(".")获取的是.首次出现的位置
			String subFieldNameSequence = fieldNameSequence.substring(fieldNameSequence.indexOf(".") + 1);
			value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
		}
		return value;
	}

	/**
	 * （导出，即list到Excel）
	 * @Desc 根据字段名获取字段值
	 * @param fieldName 字段名
	 * @param o 对象
	 * @return 返回字段值
	 * @throws Exception
	 */
	private static Object getFieldValueByName(String fieldName, Object o) throws Exception{
		//写法一：
		Field field = getFieldByName(fieldName, o.getClass());
		if(field != null){
			//如果取得的Field是private的,那么就要调用setAccessible(true),否则会报IllegalAccessException
			field.setAccessible(true);
			return field.get(o);//获取字段值
		}else{
			throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
		}
		//return null;//这行代码不能写，因为不会执行到本行代码，Unreachable statement

		//写法二：
		/*Object value;
		Field field = getFieldByName(fieldName, o.getClass());
		if(field != null){
			field.setAccessible(true);
			value = field.get(o);
		}else{
			throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
		}
		return value;*/
	}

	/**
	 * （导出，即list到Excel）
	 * @Desc 根据字段名获取字段
	 * @param fieldName 字段名
	 * @param clazz 包含该字段的类
	 * @return 返回字段
	 */
	private static Field getFieldByName(String fieldName, Class<?> clazz){
		//拿到本类的所有字段
		Field[] selfFields = clazz.getDeclaredFields();

		//如果本类中存在该字段，则返回
		for(Field field : selfFields){
			if(field.getName().equals(fieldName)){
				return field;
			}
		}

		//否则，查看父类中是否存在此字段，如果有则返回
		Class<?> superClazz = clazz.getSuperclass();
		if(superClazz != null  &&  superClazz != Object.class){
			return getFieldByName(fieldName, superClazz);
		}

		//如果本类和父类都没有，则返回空
		return null;
	}

	/**
	 * （导出，即list到Excel）
	 * @Desc 设置工作表自动列宽和首行加粗
	 * @param sheet 工作表
	 * @param extraWith 额外宽度
	 */
	private static void setColumnAutoSize(WritableSheet sheet, int extraWith){
		//获取本列的最宽单元格的宽度
		for(int column=0; column<sheet.getColumns(); column++){//column列数
			int colWith = 0;
			for(int row=0; row<sheet.getRows(); row++){//row行数
				String content = sheet.getCell(column,row).getContents();
				int cellWith = content.length();
				if(StringUtil.isChinese(content)){//如果该字段的值含有中文
					cellWith = cellWith * 2;
				}
				if(colWith < cellWith){
					colWith = cellWith;
				}
			}
			//设置单元格的宽度为最宽宽度+额外宽度
			sheet.setColumnView(column, colWith + extraWith);
		}
	}

	/**
	 * （导入，即Excel到list）
	 * @Desc 根据字段名给对象的字段赋值
	 * @param fieldName 字段名
	 * @param fieldValue 字段值
	 * @param o 对象
	 */
	private static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception {
		Field field = getFieldByName(fieldName, o.getClass());
		if(field != null){
			field.setAccessible(true);
			//获取字段类型
			Class<?> fieldType = field.getType();
			//根据字段类型给字段赋值
			if(String.class == fieldType) {
				field.set(o, String.valueOf(fieldValue));
			}else if(Integer.TYPE == fieldType || Integer.class == fieldType) {
				field.set(o, Integer.parseInt(fieldValue.toString()));
			}else if(Long.TYPE == fieldType || Long.class == fieldType) {
				field.set(o, Long.valueOf(fieldValue.toString()));
			}else if(Float.TYPE == fieldType || Float.class == fieldType) {
				field.set(o, Float.valueOf(fieldValue.toString()));
			}else if(Short.TYPE == fieldType || Short.class == fieldType) {
				field.set(o, Short.valueOf(fieldValue.toString()));
			}else if(Double.TYPE == fieldType || Double.class == fieldType) {
				field.set(o, Double.valueOf(fieldValue.toString()));
			}else if(Character.TYPE == fieldType) {
				if (fieldValue != null && fieldValue.toString().length() > 0) {
					field.set(o, fieldValue.toString().charAt(0));
				}
			}else if(Date.class == fieldType){
				field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));
			}else{
				field.set(o, fieldValue);
			}
		}else{
			throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
		}
	}

}
